-- =======================================================================
-- This stored procedure gets PreFuseURL and PostFuseURL for ContentID
-- or NavMenuID
--
-- Modifications
-- 06/03/2003  E.Tatsui   Created
-- 10/15/2003  E.Tatsui   Added NavContentDisplayPath and FolderContentDisplayPath and used them.
-- =======================================================================

CREATE    PROCEDURE [dbo].[amsp_CMGetFuseURL]
  @InNavMenuID numeric = NULL,
  @InContentID numeric = NULL,
  @OutPreFuseURL varchar(255) OUTPUT,
  @OutPostFuseURL varchar(255) OUTPUT,
  @InPreviewFlag bit = 0
AS
BEGIN
  DECLARE
    @NavContentGroupInd   char(1),
    @CategoryDepth numeric,
    @ContentSecureFlag    char(1),
    @ContentMembersOnlyFlag   char(1),
    @MembersOnlyFlag char(1),
    @ShowInTemplateFlag   char(1),
    @TemplatePath   varchar(255),
    @ContentCount   integer,
    @LinkCount      integer,
    @FileCount      integer,
    @LinkURL      varchar(255),
    @NavMenuID  numeric,
    @Name     varchar(255),
    @PublishLocation    varchar(255),
    @FileSourceLocation   varchar(255),
    @DirectListComboInd   char(1),
    @HTMLContentID    numeric,
    @ContentID      numeric,
    @l_PreFuseURL   varchar(255),
    @l_PostFuseURL    varchar(255),
    @PreFuseURL     varchar(255),
    @PostFuseURL    varchar(255),
    @URLParamChar   char(1),
    @ContentDisplayPath varchar(255),
    @NavContentDisplayPath varchar(255),
    @FolderContentDisplayPath varchar(255),
    @URLSafeName varchar(255),
    @FilePath varchar(255),
    @FileName varchar(255),
    @DestDirectory varchar(255),
    @DefaultFileName varchar(255),
    @DefaultContentID numeric,
    @PreviousContentID numeric,
    @GenerateDefaultFlag char(1),
    @GECodePath varchar(255),
    @VirtualDirectoryPath varchar(255),
    @UseAspNetTemplateFlag char(1),
  @UrlVars varchar(1000),
    @MissingIncludeURL varchar(255)
  
  SELECT @NavMenuID = a.NavMenuID,
         @CategoryDepth = a.CategoryDepth,
         @NavContentGroupInd = a.NavContentGroupInd,
         @DirectListComboInd = a.DirectListComboInd,
         @MembersOnlyFlag = a.MembersOnlyFlag,
         @Name = a.Name,
         @TemplatePath = IsNull(a.TemplatePath,''),
         @FilePath = a.FilePath,
         @NavContentDisplayPath = b.NavContentDisplayPath,
         @FolderContentDisplayPath = b.FolderContentDisplayPath,
         @DefaultFileName = b.DefaultFileName,
         @DefaultContentID = a.ContentID,
         @UseAspNetTemplateFlag = b.UseAspNetTemplateFlag
    FROM Nav_Menu a WITH (NOLOCK), Website b WITH (NOLOCK)
   WHERE NavMenuID = @InNavMenuID
     AND a.WebsiteKey = b.WebsiteKey

  SET @GenerateDefaultFlag = 'N'
  SET @UrlVars = ''

  IF @NavContentGroupInd = 'N'
    SET @ContentDisplayPath = IsNull(@NavContentDisplayPath,'')
  ELSE
    SET @ContentDisplayPath = IsNull(@FolderContentDisplayPath,'')

  SELECT @GECodePath = Value
    FROM System_Variable
   WHERE Name = 'GECodePath'

  SELECT @VirtualDirectoryPath = Value
    FROM System_Variable
   WHERE Name = 'VirtualDirectoryPath'

  IF Len(@GECodePath) > 1
    SET @GECodePath = RIGHT(@GECodePath,Len(@GECodePath)-1)
  ELSE IF @GECodePath = '/'
    SET @GECodePath = ''

  IF Len(@VirtualDirectoryPath) > 1
    SET @VirtualDirectoryPath = RIGHT(@VirtualDirectoryPath,Len(@VirtualDirectoryPath)-1)
  ELSE IF @VirtualDirectoryPath = '/'
    SET @VirtualDirectoryPath = ''

  -- We are running this for a specific ContentID
  IF @InContentID IS NOT NULL BEGIN
    SET @ContentCount = 1
    SELECT @ShowInTemplateFlag = c.ShowInTemplateFlag,
           @PublishLocation = c.PublishLocation,
           @ContentMembersOnlyFlag = c.MembersOnlyFlag,
           @ContentSecureFlag = c.SecureFlag,
           @LinkURL = (SELECT TOP 1 LinkURL
                         FROM Content_Link z WITH (NOLOCK)
                        WHERE z.ContentID = c.ContentID),
        @FileName = (SELECT TOP 1 FileName
                                     FROM Content_File z WITH (NOLOCK)
                                    WHERE z.ContentID = c.ContentID),
           @HTMLContentID = ch.ContentID,
           @LinkCount = (SELECT Count(*) FROM Content_Link WITH (NOLOCK) WHERE ContentID = c.ContentID),
           @FileCount = (SELECT Count(*) FROM Content_File WITH (NOLOCK) WHERE ContentID = c.ContentID),
           @ContentID = c.ContentID,
           @URLSafeName = c.URLSafeName,
           @PreviousContentID = c.PreviousContentID
    FROM Content c WITH (NOLOCK) LEFT OUTER JOIN Content_HTML ch WITH (NOLOCK)

      ON c.ContentID = ch.ContentID
   WHERE c.ContentID = @InContentID

   IF @NavContentGroupInd = 'N'
      AND (@DefaultContentID = @InContentID OR @PreviousContentID = @DefaultContentID)
     SET @GenerateDefaultFlag = 'Y'

  END
  -- We're running this for already published menu item.
  ELSE BEGIN
    SET @GenerateDefaultFlag = 'Y'
    
    IF @InPreviewFlag = 0
      SELECT @ContentCount = Count(*)
        FROM Content c WITH (NOLOCK)
       WHERE NavMenuID = @InNavMenuID
         AND c.WorkflowStatusCode = 'P'
         AND c.PublishDateTime <= CURRENT_TIMESTAMP
         AND (c.ExpirationDate >= CURRENT_TIMESTAMP
               OR c.ArchiveAtExpirationFlag IS NULL
               OR c.ArchiveAtExpirationFlag = 'N')
    ELSE
      SELECT @ContentCount = Count(*)
        FROM vCurrent_Content c WITH (NOLOCK)
       WHERE NavMenuID = @InNavMenuID

    -- Get the default content.
    SELECT @ShowInTemplateFlag = c.ShowInTemplateFlag,
           @PublishLocation = c.PublishLocation,
           @ContentMembersOnlyFlag = c.MembersOnlyFlag,
           @ContentSecureFlag = c.SecureFlag,
           @LinkURL = (SELECT TOP 1 LinkURL
                         FROM Content_Link z
                        WHERE z.ContentID = c.ContentID),
           @FileName = (SELECT TOP 1 FileName
                                     FROM Content_File z
                                    WHERE z.ContentID = c.ContentID),
           @HTMLContentID = ch.ContentID,
           @LinkCount = (SELECT Count(*) FROM Content_Link WITH (NOLOCK) WHERE ContentID = c.ContentID),
           @FileCount = (SELECT Count(*) FROM Content_File WITH (NOLOCK) WHERE ContentID = c.ContentID),
           @ContentID = c.ContentID,
           @URLSafeName = c.URLSafeName
      FROM Content c WITH (NOLOCK) LEFT OUTER JOIN Content_HTML ch WITH (NOLOCK)
        ON c.ContentID = ch.ContentID, Nav_Menu nm WITH (NOLOCK)
     WHERE c.NavMenuID = @InNavMenuID
       AND nm.ContentID = c.ContentID
  END
  
  IF (@NavContentGroupInd = 'N') BEGIN
  IF @UseAspNetTemplateFlag = 'N' BEGIN
    IF @ShowInTemplateFlag = 'Y' OR @ShowInTemplateFlag IS NULL
      SET @l_PreFuseURL = @GECodePath + 'Template.cfm'
    ELSE
      SET @l_PreFuseURL = @GECodePath + 'AMTemplate.cfm'

    SET @l_PreFuseURL = @l_PreFuseURL + '?Section=' + @Name + '&Template='
    SET @URLParamChar = '&'
    END
  ELSE BEGIN
    SET @UrlVars = @UrlVars + 'Section=' + @Name
    IF @ShowInTemplateFlag = 'Y' OR @ShowInTemplateFlag IS NULL
      SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/'
    ELSE BEGIN
      SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/'
      SET @UrlVars = @UrlVars + '&NoTemplate=1'
    END
    SET @URLParamChar = '?'
  END
  SET @DestDirectory = @ContentDisplayPath + @FilePath
  END
  ELSE BEGIN -- Content Folder
    IF (@UseAspNetTemplateFlag = 'N')BEGIN
    SET @l_PreFuseURL = @GECodePath + 'TemplateRedirect.cfm' + '?Template='
    SET @URLParamChar = '&'
  END
    
  ELSE BEGIN -- ASP.NET TEmplate
    SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/TemplateRedirect.aspx?Template=/ContentManagerNet/'
    SET @URLParamChar = '&'
  END
  SET @DestDirectory =  @ContentDisplayPath + @FilePath
  END

  IF (@InContentID IS NOT NULL)
  SET @DirectListComboInd = 'D'

  /* Prepare Missing Include URL */
  IF @UseAspNetTemplateFlag = 'N'
  SET @MissingIncludeURL = @l_PreFuseURL + '/MissingInclude.htm'
  ELSE
    SET @MissingIncludeURL = @l_PreFuseURL + 'MissingInclude.aspx' + CASE WHEN Len(@UrlVars) > 0 THEN '?' + @UrlVars ELSE '' END

  /*
  ** A nav menu item will either have no, 1, or multiple content records associated with it.
  */

  /* No content to point to. */

  IF (@ContentCount = 0)
    BEGIN
    SET @l_PreFuseURL = @MissingIncludeURL
      SET @l_PostFuseURL = @l_PreFuseURL
    END

  /*
  ** If the default content record or nav menu record is flagged Members Only,
  ** Pre- and PostFuseURL will go through MembersOnly.cfm.
  */

  ELSE IF ( (@ContentCount = 1 OR @ContentCount > 1) AND
             (@MembersOnlyFlag = 'Y' OR @ContentMembersOnlyFlag = 'Y') )
  BEGIN
    /* If content is set to display outside template, use AMTemplate.cfm */
  IF(@UseAspNetTemplateFlag = 'N') BEGIN
    IF (@ShowInTemplateFlag = 'N')
    BEGIN
      SET @l_PreFuseURL = @GECodePath + 'AMTemplate.cfm?Section=' + @Name + '&Template=' + '/MembersOnly.cfm&' + 'NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID) + '&DirectListComboInd=' + @DirectListComboInd
      SET @l_PostFuseURL = @l_PreFuseURL
    END
    ELSE IF (@ShowInTemplateFlag = 'Y')
    BEGIN -- Not show inside the template.
      SET @l_PreFuseURL = @l_PreFuseURL + '/MembersOnly.cfm' + @URLParamChar + 'NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID) + '&DirectListComboInd=' + @DirectListComboInd
      SET @l_PostFuseURL = @l_PreFuseURL
    END
  END
  ELSE BEGIN -- ASP.NET Template
    IF (@ShowInTemplateFlag = 'N')
    BEGIN
      SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/MembersOnly.aspx?NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID) + '&DirectListComboInd=' + @DirectListComboInd + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
      SET @l_PostFuseURL = @l_PreFuseURL
    END
    ELSE IF (@ShowInTemplateFlag = 'Y')
    BEGIN -- Not show inside the template.
      SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/MembersOnly.aspx?NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID) + '&DirectListComboInd=' + @DirectListComboInd + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
      SET @l_PostFuseURL = @l_PreFuseURL
    END
  END
  END

  /*
  ** One content record and multiple content records with the DirectListComboInd set to
  ** "D" are essentially the same. In the latter case, you go directly to the page and

  ** rely on navigation within the text to point to the other content.
  */

  ELSE IF ( (@ContentCount = 1) OR (@ContentCount > 1 AND @DirectListComboInd = 'D') )
  BEGIN

    /* If content record has no related links, files, or HTML, show MissingInclude.htm. */
    IF (@LinkCount = 0 AND @FileCount = 0 AND @HTMLContentID IS NULL)
    BEGIN
      SET @l_PreFuseURL = @MissingIncludeURL
      SET @l_PostFuseURL = @l_PreFuseURL
    END

    /* Single piece of content consists of a link. */
    ELSE IF (@LinkCount = 1 AND @FileCount = 0 AND @HTMLContentID IS NULL)
    BEGIN
      SET @LinkURL = Rtrim(Ltrim(@LinkURL))
      IF (Left(@LinkURL,1) = '/')  AND (CHARINDEX('TEMPLATEREDIRECT.CFM',UPPER(@LinkURL)) =0)
      BEGIN
        IF ((CHARINDEX('SECTION=', UPPER(@LinkURL)) = 0)
            AND (CHARINDEX('.CFM', UPPER(@LinkURL)) != 0 OR CHARINDEX('.HTM', UPPER(@LinkURL)) != 0 OR CHARINDEX('.GIF', UPPER(@LinkURL)) != 0 OR CHARINDEX('.JPG', UPPER(@LinkURL)) != 0 )
            AND (@ShowInTemplateFlag = 'Y'))
        BEGIN
            IF (@UseAspNetTemplateFlag = 'N')
                SET @l_PreFuseURL = @GECodePath + 'Template.cfm' + '?Section=' + @Name + '&Template=' + @LinkURL
            ELSE
                SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/Default.aspx?Section=' + @Name + '&Template=' + @LinkURL
        END
        ELSE IF ((CHARINDEX('SECTION=', UPPER(@LinkURL)) = 0)
            AND (CHARINDEX('.CFM', UPPER(@LinkURL)) != 0 OR CHARINDEX('.GIF', UPPER(@LinkURL)) != 0 OR CHARINDEX('.JPG', UPPER(@LinkURL)) != 0 )
            AND (@ShowInTemplateFlag = 'N'))
        BEGIN
            IF (@UseAspNetTemplateFlag = 'N')
                SET @l_PreFuseURL = @GECodePath + 'AMTemplate.cfm?Section=' + @Name + '&Template=' + @LinkURL
            ELSE
                SET @l_PreFuseURL = @GECodePath + 'ContentManagerNet/Default.aspx?Section=' + @Name + '&NoTemplate=1&Template=' + @LinkURL
        END
        ELSE IF ((CHARINDEX('.ASPX', UPPER(@LinkURL)) != 0))BEGIN
            IF (@UseAspNetTemplateFlag = 'N')
                SET @l_PreFuseURL = @l_PreFuseURL + '/CM/ContentDisplay.cfm' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID)
            ELSE
                SET @l_PreFuseURL = @l_PreFuseURL + 'ContentDisplay.aspx' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID) + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
        END
        ELSE BEGIN
           SET @l_PreFuseURL = @LinkURL
        END
      END

      ELSE BEGIN
        IF (Upper(Left(@LinkURL, 4)) = 'HTTP') OR ((CHARINDEX('TEMPLATEREDIRECT.CFM',UPPER(@LinkURL)) != 0))
           SET @l_PreFuseURL = @LinkURL
        ELSE
           SET @l_PreFuseURL = 'http://' + @LinkURL
      END

      /* PostFuseURL will equal the PreFuseURL if content is a link. */
      SET @l_PostFuseURL = @l_PreFuseURL
    END

    /* Single piece of content consists of a file. */
    ELSE IF (@FileCount = 1 AND @LinkCount = 0 AND @HTMLContentID IS NULL)
    BEGIN
      IF (@UseAspNetTemplateFlag = 'N')
    SET @l_PreFuseURL = @l_PreFuseURL + '/CM/ContentDisplay.cfm' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID)
      ELSE
    SET @l_PreFuseURL = @l_PreFuseURL + 'ContentDisplay.aspx' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID) + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
      IF @NavContentGroupInd = 'C'
        SET @l_PostFuseURL = @l_PreFuseURL
      --Only when we are figuring out default page for a navigation, set it to generated defaulg page.
      ELSE IF @GenerateDefaultFlag = 'Y' AND @DefaultFileName IS NOT NULL AND @InContentID IS NULL
        SET @l_PostFuseURL = @DestDirectory + @DefaultFileName
      ELSE
        SET @l_PostFuseURL = @DestDirectory + @FileName
    END

    /* Single piece of content is an HTML record. */
    ELSE IF (@HTMLContentID IS NOT NULL AND @FileCount = 0 AND @LinkCount = 0) BEGIN
    IF(@UseAspNetTemplateFlag = 'N')
    SET @l_PreFuseURL = @l_PreFuseURL + '/CM/HTMLDisplay.cfm' + @URLParamChar + 'ContentID=' + convert(varchar(10), @HTMLContentID)
      ELSE
    SET @l_PreFuseURL = @l_PreFuseURL + 'HTMLDisplay.aspx' + @URLParamChar + 'ContentID=' + convert(varchar(10), @HTMLContentID) + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
    IF @NavContentGroupInd = 'C'
        SET @l_PostFuseURL = @l_PreFuseURL
      -- Only if we are generating default file for single piece of content (not the one for combo or list),
      -- use default name.
      ELSE IF @GenerateDefaultFlag = 'Y' AND @DefaultFileName IS NOT NULL AND @DirectListComboInd = 'D'
        SET @l_PostFuseURL = @DestDirectory + @DefaultFileName
      ELSE
        SET @l_PostFuseURL = @DestDirectory + @URLSafeName + '.htm'
    END
    
    /* Single piece of content is a mix of links, files, and/or HTML. */
    ELSE BEGIN
    IF @UseAspNetTemplateFlag = 'N'
    SET @l_PreFuseURL = @l_PreFuseURL + '/CM/ContentDisplay.cfm' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID)
      ELSE
    SET @l_PreFuseURL = @l_PreFuseURL + 'ContentDisplay.aspx' + @URLParamChar + 'ContentID=' + convert(varchar(10), @ContentID) + CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END
      IF @NavContentGroupInd = 'C'
        SET @l_PostFuseURL = @l_PreFuseURL
      ELSE IF @GenerateDefaultFlag = 'Y' AND @DefaultFileName IS NOT NULL
        SET @l_PostFuseURL = @DestDirectory + @DefaultFileName
      ELSE
        SET @l_PostFuseURL = @DestDirectory + @URLSafeName + '.htm'

    END
  END -- ENDS @DirectListComboInd = 'D'

  /* Multiple content records and DirectListComboInd set to "C". */
  ELSE IF (@ContentCount > 1 AND @DirectListComboInd = 'C')
  BEGIN
    /* If content record has no related links, files, or HTML, show MissingInclude.htm. */
    IF (@LinkCount = 0 AND @FileCount = 0 AND @HTMLContentID IS NULL)
    BEGIN
    SET @l_PreFuseURL = @MissingIncludeURL
      SET @l_PostFuseURL = @l_PreFuseURL
    END
    ELSE BEGIN
    IF @UseAspNetTemplateFlag = 'N'
    SET @l_PreFuseURL = @l_PreFuseURL + '/CM/ContentCombo.cfm' + @URLParamChar + 'NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID)
    ELSE
        SET @l_PreFuseURL = @l_PreFuseURL + 'ContentCombo.aspx' + @URLParamChar + 'NavMenuID=' + convert(varchar(10), @NavMenuID) + '&ContentID=' + convert(varchar(10), @ContentID) + (CASE WHEN Len(@UrlVars) > 0 THEN '&' + @UrlVars ELSE '' END)
      IF @NavContentGroupInd = 'C'
        SET @l_PostFuseURL = @l_PreFuseURL
      ELSE IF @GenerateDefaultFlag = 'Y' AND @DefaultFileName IS NOT NULL
        SET @l_PostFuseURL = @DestDirectory + @DefaultFileName
      ELSE
        SET @l_PostFuseURL = @DestDirectory + 'Default' + CONVERT(varchar(20),@InNavMenuID) + '.htm'
    END
  END

  /* Multiple content records and DirectListComboInd set to "L". */
  ELSE IF (@ContentCount > 1 AND @DirectListComboInd = 'L')
  BEGIN
    /* If content record has no related links, files, or HTML, show MissingInclude.htm. */
    IF (@LinkCount = 0 AND @FileCount = 0 AND @HTMLContentID IS NULL)
    BEGIN
      SET @l_PreFuseURL = @MissingIncludeURL
      SET @l_PostFuseURL = @l_PreFuseURL
    END
    ELSE BEGIN
      IF @UseAspNetTemplateFlag = 'N'
    SET @l_PreFuseURL = @l_PreFuseURL + '/CM/ContentList.cfm' + @URLParamChar + 'NavMenuID=' + convert(varchar(10), @NavMenuID)
      ELSE
        SET @l_PreFuseURL = @l_PreFuseURL + 'ContentList.aspx' + @URLParamChar + 'NavMenuID=' + convert(varchar(10), @NavMenuID) + '&' + @UrlVars
      IF @GenerateDefaultFlag = 'Y' AND @DefaultFileName IS NOT NULL
        SET @l_PostFuseURL = @DestDirectory + @DefaultFileName
      ELSE
        SET @l_PostFuseURL = @DestDirectory + 'Default' + CONVERT(varchar(20),@InNavMenuID) + '.htm'
    END
  END
  
  IF(@UseAspNetTemplateFlag = 'Y' AND CHARINDEX('templateredirect',LOWER(@l_PostFuseURL)) = 0 AND CHARINDEX('.aspx',LOWER(@l_PostFuseURL)) > 0 AND LEFT(@l_PostFuseURL,LEN(@VirtualDirectoryPath)) = @VirtualDirectoryPath)
  BEGIN
    IF (@VirtualDirectoryPath <> '')
      SET @l_PostFuseURL = @VirtualDirectoryPath + '/' + @Name + SUBSTRING(@l_PostFuseURL,LEN(@VirtualDirectoryPath) + 1, LEN(@l_PostFuseURL) - LEN(@VirtualDirectoryPath))
    ELSE
      SET @l_PostFuseURL = '/' + @Name + '/' + @l_PostFuseURL
  END
  
  IF LEFT(@l_PostFuseURL,1) = '/'
    SET @l_PostFuseURL = SUBSTRING(@l_PostFuseURL,2,Len(@l_PostFuseURL)-1)

  IF LEFT(@l_PreFuseURL,1) = '/'
    SET @l_PreFuseURL = SUBSTRING(@l_PreFuseURL,2,Len(@l_PreFuseURL)-1)

  SET @OutPreFuseURL = @l_PreFuseURL
  SET @OutPostFuseURL = @l_PostFuseURL

END

GO